﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using Pantry.Commons;
using Pantry.Entity;

namespace Pantry.DataAccess
{
    public class TeamDao:PantryDao
    {
        public TeamDao()
        {
        }

        public TeamDao(string connectionString):base(new SqlConnection(connectionString))
        {

        }
            
        public override DataTable RetrieveAllData()
        {
            return RetrieveData(DBSchemas.SqlRetrieveAllTeams);
        }

        public override DataTable RetrieveData(string conditionField,string conditionValue,SqlDbType sqlType)
        {
            var sqlCommand = String.Format(CultureInfo.CurrentCulture, DBSchemas.SqlRetrieveTeamsByDepartment,
                                           conditionField);
            var parameters = new SqlParameter[1];
            parameters[0] = new SqlParameter("@ConditionField", sqlType) { Value = conditionValue };
            return RetrieveData(sqlCommand, parameters);
        }

        public override object RetrieveEntity(int id)
        {
            if (id < 1)
            {
                return null;
            }

            var parameter = new SqlParameter("@TeamId", SqlDbType.Int) { Value = id };
            var dataTable = RetrieveData(DBSchemas.SqlRetrieveTeam, parameter);
            if (dataTable == null)
            {
                return null;
            }

            var dataRow = dataTable.Rows[0];
            var objContactId = dataRow["ContactId"].ToString();
            var objTypeOfBusinessId = dataRow["TypeOfBusinessId"].ToString();
            var team = new Team
            {
                TeamName = (string)dataRow["TeamName"],
                ShortDescription = (string)dataRow["ShortDescription"],
                FullDescription = (string)dataRow["FullDescription"],
                DepartmentId = (int)dataRow["DepartmentId"],
                ContactId = string.IsNullOrEmpty(objContactId) ? -1 : int.Parse(objContactId, CultureInfo.InvariantCulture),
                TypeOfBusinessId = string.IsNullOrEmpty(objTypeOfBusinessId) ? -1 : int.Parse(objTypeOfBusinessId, CultureInfo.InvariantCulture),
                AddressLine1 = (string)dataRow["AddressLine1"],
                AddressLine2 = (string)dataRow["AddressLine2"],
                AddressLine3 = (string)dataRow["AddressLine3"],
                Postcode = (string)dataRow["Postcode"],
                Town = (string)dataRow["Town"],
                County = (string)dataRow["County"],
                CountryId = (int)dataRow["CountryId"],
                Phone = (string)dataRow["Phone"],
                Fax = (string)dataRow["Fax"],
                Email = (string)dataRow["Email"],
                WebAddress = (string)dataRow["WebAdress"],
                IsActive = (bool)dataRow["IsActive"],
                Date = (DateTime)dataRow["Date"],
                TeamId = (int)dataRow["TeamId"],
                OrganizationId = (int)dataRow["OrganizationId"],
                ContactName = dataRow["ContactName"].ToString(),
                TypeName = dataRow["TypeName"].ToString(),
                SicCode = dataRow["SicCode"].ToString()
            };
            return team;
        }

        public override int Insert(object sender)
        {
            var team = (Team)sender;
            if (team==null)
            {
                return 0;
            }

            var result = CheckUnique(@"Team", @"TeamName", team.TeamName);
            if (result > 0)
            {
                return -2;
            }

            var parameters = new SqlParameter[19];
            parameters[0] = new SqlParameter("@TeamName", SqlDbType.VarChar, 50) { Value = team.TeamName };
            parameters[1] = new SqlParameter("@ShortDescription", SqlDbType.VarChar, 100) { Value = team.ShortDescription };
            parameters[2] = new SqlParameter("@FullDescription", SqlDbType.VarChar, 200) { Value = team.FullDescription };
            parameters[3] = new SqlParameter("@DepartmentId", SqlDbType.Int) { Value = team.DepartmentId };
            parameters[4] = new SqlParameter("@ContactId", SqlDbType.Int) { Value = team.ContactId };
            parameters[5] = new SqlParameter("@TypeOfBusinessId", SqlDbType.Int) { Value = team.TypeOfBusinessId };
            parameters[6] = new SqlParameter("@AddressLine1", SqlDbType.VarChar, 100) { Value = team.AddressLine1 };
            parameters[7] = new SqlParameter("@AddressLine2", SqlDbType.VarChar, 100) { Value = team.AddressLine2 };
            parameters[8] = new SqlParameter("@AddressLine3", SqlDbType.VarChar, 100) { Value = team.AddressLine3 };
            parameters[9] = new SqlParameter("@Postcode", SqlDbType.VarChar, 20) { Value = team.Postcode };
            parameters[10] = new SqlParameter("@Town", SqlDbType.VarChar, 50) { Value = team.Town };
            parameters[11] = new SqlParameter("@County", SqlDbType.VarChar, 50) { Value = team.County };
            parameters[12] = new SqlParameter("@CountryId", SqlDbType.Int) { Value = team.CountryId };
            parameters[13] = new SqlParameter("@Phone", SqlDbType.VarChar, 20) { Value = team.Phone };
            parameters[14] = new SqlParameter("@Fax", SqlDbType.VarChar, 20) { Value = team.Fax };
            parameters[15] = new SqlParameter("@Email", SqlDbType.VarChar, 50) { Value = team.Email };
            parameters[16] = new SqlParameter("@WebAdress", SqlDbType.VarChar, 100) { Value = team.WebAddress };
            parameters[17] = new SqlParameter("@IsActive", SqlDbType.Bit) { Value = team.IsActive };
            parameters[18] = new SqlParameter("@Date",SqlDbType.VarChar){Value = team.Date};
            return ExecuteNonQuery(DBSchemas.SqlInsertTeam, parameters);
        }

        public override int Update(object sender)
        {
            var team = (Team)sender;
            if (team==null)
            {
                return 0;
            }

            var result = CheckUniqueForAmend(@"Team", @"TeamName", team.TeamName,@"TeamId",team.TeamId);
            if (result != 0)
            {
                return -2;
            }

            var parameters = new SqlParameter[20];
            parameters[0] = new SqlParameter("@TeamName", SqlDbType.VarChar, 50) { Value = team.TeamName };
            parameters[1] = new SqlParameter("@ShortDescription", SqlDbType.VarChar, 200) { Value = team.ShortDescription };
            parameters[2] = new SqlParameter("@FullDescription", SqlDbType.VarChar, 500) { Value = team.FullDescription };
            parameters[3] = new SqlParameter("@DepartmentId", SqlDbType.Int) { Value = team.DepartmentId };
            parameters[4] = new SqlParameter("@ContactId", SqlDbType.Int) { Value = team.ContactId };
            parameters[5] = new SqlParameter("@TypeOfBusinessId", SqlDbType.Int) { Value = team.TypeOfBusinessId };
            parameters[6] = new SqlParameter("@AddressLine1", SqlDbType.VarChar, 100) { Value = team.AddressLine1 };
            parameters[7] = new SqlParameter("@AddressLine2", SqlDbType.VarChar,100) { Value = team.AddressLine2 };
            parameters[8] = new SqlParameter("@AddressLine3", SqlDbType.VarChar, 100) { Value = team.AddressLine3 };
            parameters[9] = new SqlParameter("@Postcode", SqlDbType.VarChar, 20) { Value = team.Postcode };
            parameters[10] = new SqlParameter("@Town", SqlDbType.VarChar, 50) { Value = team.Town };
            parameters[11] = new SqlParameter("@County", SqlDbType.VarChar, 50) { Value = team.County };
            parameters[12] = new SqlParameter("@CountryId", SqlDbType.Int) { Value = team.CountryId };
            parameters[13] = new SqlParameter("@Phone", SqlDbType.VarChar,20) { Value = team.Phone };
            parameters[14] = new SqlParameter("@Fax", SqlDbType.VarChar,20) { Value = team.Fax };
            parameters[15] = new SqlParameter("@Email", SqlDbType.VarChar,100) { Value = team.Email };
            parameters[16] = new SqlParameter("@WebAdress", SqlDbType.VarChar,100) { Value = team.WebAddress };
            parameters[17] = new SqlParameter("@IsActive", SqlDbType.Bit) { Value = team.IsActive };
            parameters[18] = new SqlParameter("@Date",SqlDbType.VarChar){Value = team.Date};
            parameters[19] = new SqlParameter("@TeamId", SqlDbType.Int) { Value = team.TeamId };
            return ExecuteNonQuery(DBSchemas.SqlUpdateTeam, parameters);
        }
    }
}
